package uk.ac.cam.echo.server.integrationtests;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.List;

import junit.framework.Assert;

import org.junit.Test;

import uk.ac.cam.echo.ScheduleEvent;
import uk.ac.cam.echo.server.Database;

/**
 * @author Tom Smith
 */
public class TestScheduleTable extends TestDatabase {

	@Test
	/**
	 * Check that an event is inserted into the database, and can be retrieved correctly.
	 */
	public void getCorrectEvent() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage','Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(1, 'Main Event', '3000-06-10 23:00:00', NULL, 1, 'Description');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<ScheduleEvent> list = super.m_db.getSchedule(10, null);

			// Correct number of events.
			Assert.assertEquals(1, list.size());
			
			ScheduleEvent event = list.get(0);
			
			Assert.assertEquals("Main Event", event.getEventName());
			Assert.assertEquals("Description", event.getEventDescription());
			
			// Create a calendar to compare to.
			Calendar cal = Calendar.getInstance();
			// N.B. Calendar starts month from 0, while SQL starts from 1.
			cal.set(3000, 05, 10, 23, 00, 00);
			cal.set(Calendar.MILLISECOND, 0);

			Assert.assertEquals(cal.getTimeInMillis(), event.getStartTime());
			
			Assert.assertEquals(0, event.getEndTime());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * Check that only events in the future are retrieved.
	 */
	public void getFutureEvents() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage','Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(1, 'Old Event', '1970-01-01 00:00:00', '1970-01-01 01:00:00', 1, 'Description');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(2, 'Future Event', '3000-06-10 23:00:00', '3000-06-11 00:00:00', 1, 'Description');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<ScheduleEvent> list = super.m_db.getSchedule(10, null);

			// Correct number of events.
			Assert.assertEquals(1, list.size());
			
		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * Check that only events for the correct location are returned.
	 */
	public void getScheduleByLocation() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblLocation + " VALUES('1', 'Main Court');");
			sqlStatement.execute("INSERT INTO " + Database.tblLocation + " VALUES('7', 'Seventh Court');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, '1', 'Main Stage','Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(2, '7', 'Bar','Drink');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(3, '7', 'Burgers','Food');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(1, 'Main Event', '3000-06-10 23:00:00', NULL, 1, '');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(2, 'Drinks', '3000-06-10 23:00:00', NULL, 3, '');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(3, 'Food', '3000-06-10 23:00:00', NULL, 2, '');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<ScheduleEvent> list = super.m_db.getSchedule(10, "1");

			// Correct number of events - only one scheduled in location "1"
			Assert.assertEquals(1, list.size());
			
			ScheduleEvent event = list.get(0);
			
			Assert.assertEquals("Main Event", event.getEventName());
			

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test(expected=SQLException.class)
	/**
	 * Invalid stage.
	 */
	public void invalidStage() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage','Stage');");
		sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
				"VALUES(1, 'Main Event', '2012-06-10 23:00:00', NULL, 2, NULL);");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Invalid stage ID specified for an event.");
	}

	@Test(expected=SQLException.class)
	/**
	 * Invalid stage.
	 */
	public void noEventName() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage','Stage');");
		sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
				"VALUES(1, NULL, '2012-06-10 23:00:00', NULL, 1, NULL);");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("EventName is NULL.");
	}
	
	@Test(expected=SQLException.class)
	/**
	 * Start time after end time.
	 */
	public void startTimeAfterEndTime() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
		sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
				"VALUES(1, 'Main Event', '2012-06-11 00:00:00', '2012-06-10 23:00:00', 1, 'Description');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Start time is after end time.");
	}
	
	@Test
	/**
	 * Start time before end time.
	 */
	public void startTimeBeforeEndTime() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(1, 'Main Event', '2012-06-10 23:00:00', '2012-06-11 00:00:00', 1, 'Description');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test(expected=SQLException.class)
	/**
	 * Start time equal to end time.
	 */
	public void startTimeEqualToEndTime() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
		sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
				"VALUES(1, 'Main Event', '2012-06-11 00:00:00', '2012-06-11 00:00:00', 1, 'Description');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Start time is equal to end time.");
	}
	
	@Test
	/**
	 * Event on an existing stage.
	 */
	public void validEvent() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + " VALUES(1, NULL, 'Main Stage', 'Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblSchedule + " " +
					"VALUES(1, 'Main Event', '2012-06-10 23:00:00', NULL, 1, 'Description');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
}
